Pivoting and Unpivoting Data in Power BI

Course: Microsoft Power BI Training

Topic: Pivoting and Unpivoting Data Using Power Query Editor

Course Link: Microsoft Power BI Training

Demo Link: Register for a Course Demo

Learning Resource: JustAcademy

1. Introduction to Pivoting and Unpivoting Data

Data transformation is an important part of preparing datasets for reporting and analysis in Microsoft Power BI. Data collected from spreadsheets, databases, business applications, and other sources may not always be structured in a format that is convenient for analysis.

For example, a sales report may contain separate columns for January, February, and March. Although this format may be easy to read in a spreadsheet, it can be difficult to maintain when new months are added or when the data needs to be analyzed using flexible Power BI visuals.

Power Query provides two useful transformations for changing the structure of a dataset: Pivot Column and Unpivot Columns.

• Pivoting: Converts values from rows into separate columns, often using an aggregation when multiple records share the same grouping and pivot values.
• Unpivoting: Converts selected columns into attribute-value rows, creating a more normalized, analysis-friendly structure.

Understanding these transformations helps you clean data, standardize reports, build flexible data models, and prepare datasets for dashboards and business intelligence analysis.

2. Learning Objectives

After completing this topic, you will be able to:

• Understand the meaning of pivoting and unpivoting data.
• Identify when to use Pivot Column and Unpivot Columns in Power Query.
• Transform row-based data into a column-based layout.
• Convert multiple value columns into attribute-value pairs.
• Choose an appropriate aggregation function for pivot operations.
• Understand the difference between Unpivot Columns and Unpivot Other Columns.
• Handle null values, duplicate records, and inconsistent data types.
• Use pivoting and unpivoting in practical business reporting scenarios.
• Validate transformed datasets before loading them into Power BI.

3. Understanding the Structure of Data

3.1 Wide-Format Data

Wide-format data stores multiple categories or periods in separate columns. This format is common in Excel reports and manually prepared business spreadsheets.

Example:

In this table, every month has its own column. If a new month is added, the structure must expand with another column.

3.2 Long-Format Data

Long-format data stores repeated categories as values in a column. Each record represents an observation for a particular entity and category.

Example:

This structure is often more convenient for filtering, grouping, visualization, and time-based analysis. Unpivoting is a common way to convert wide-format data into long-format data.

3.3 Why Data Structure Matters

The structure of a dataset affects how easily you can create reports and maintain data transformations. A wide-format table may be useful for a presentation, while a long-format table is often better for building reusable analytical models.

• Long-format data makes it easier to add new categories without creating a new column for each category.
• Wide-format data can be useful when users need a matrix with categories displayed across columns.
• Power Query can convert between these structures as part of a repeatable data preparation process.

4. What Is Pivoting Data?

Pivoting is a Power Query transformation that converts distinct values from one column into separate columns. The values from another column are placed into those new columns.

For example, a dataset with Department, Month, and Sales columns can be pivoted so that each month becomes a separate column and the sales values appear under the corresponding month.

4.1 Source Data Before Pivoting

4.2 Result After Pivoting

The Month column has been transformed into two columns: January and February. The Sales values are distributed into the appropriate month columns.

4.3 When Should You Use Pivoting?

• When creating a cross-tab report.
• When monthly or quarterly values must appear as separate columns.
• When preparing a summary table for presentation.
• When transforming category-value data into a matrix-like structure.
• When a downstream system requires a column for each category.

5. How to Pivot a Column in Power Query

Follow these steps to pivot a column using Power Query Editor.

1. Open Power BI Desktop.
2. Select Home > Transform data to open Power Query Editor.
3. Select the query containing the data you want to transform.
4. Review the column names and data types to ensure that the data is correctly structured.
5. Select the column whose distinct values should become new columns. For example, select Month.
6. Open the Transform tab on the ribbon.
7. Select Pivot Column.
8. In the dialog box, choose the column containing the values to place in the new columns, such as Sales.
9. Choose an appropriate aggregation option if the dialog provides one.
10. Select OK to apply the transformation.
11. Review the resulting columns, values, nulls, and row counts.
12. Select Close & Apply when the result is correct.

Important: Power Query retains the other columns as grouping columns. These columns help determine which source rows belong to the same output row. Check that the remaining columns represent the intended grouping level.

6. Understanding Aggregation During Pivoting

Sometimes the source dataset contains more than one record for the same combination of grouping columns and pivot value. For example, a department may have multiple January sales transactions.

When multiple values must be placed into one output cell, Power Query may require an aggregation function. The correct function depends on the meaning of the data.

6.1 Common Aggregation Options

6.2 Example of Duplicate Pivot Values

Suppose the source data contains the following records:

If the required output is one row per department, using Sum produces:

The January values have been added together. Choosing Don't Aggregate when duplicate combinations exist may produce an error because Power Query cannot place multiple distinct values into one cell without an aggregation rule.

Best practice: Do not select Sum, Count, or another aggregation automatically. First determine whether the source rows represent transactions, duplicate records, snapshots, or already summarized totals.

7. What Is Unpivoting Data?

Unpivoting converts selected columns into rows. Power Query creates an attribute column containing the original column names and a value column containing the corresponding cell values.

This is particularly useful when datasets have repeated columns for months, products, departments, or other categories.

7.1 Source Data Before Unpivoting

7.2 Result After Unpivoting

The original month columns are now represented as values in the Attribute column. Their corresponding amounts appear in the Value column.

7.3 When Should You Use Unpivoting?

• When monthly values are stored in separate columns.
• When preparing spreadsheet reports for Power BI analysis.
• When combining data from different periods into a consistent structure.
• When building trend charts and time-based visualizations.
• When the source schema changes frequently as new categories are added.
• When creating reusable reports that must work with newly added period columns.

8. How to Unpivot Columns in Power Query

1. Open Power BI Desktop and select Home > Transform data.
2. Select the required query in Power Query Editor.
3. Identify the columns that describe each entity, such as Employee, Department, or Region.
4. Select the columns containing repeated values, such as January, February, and March.
5. Open the Transform tab.
6. Select Unpivot Columns.
7. Power Query creates two columns, typically named Attribute and Value.
8. Rename Attribute to a meaningful name such as Month.
9. Rename Value to a meaningful name such as Sales.
10. Set the correct data types for the new columns.
11. Check for null values and unexpected records.
12. Select Close & Apply to load the transformed data into Power BI.

Tip: You can also select the columns that should remain unchanged and use Unpivot Other Columns. This can be more convenient when the table contains many period columns or when new period columns are added regularly.

9. Unpivot Columns vs. Unpivot Other Columns

Power Query provides different unpivot options. The correct option depends on whether you want to select the columns to transform or the columns to preserve.

9.1 Example of Unpivot Other Columns

Suppose a dataset contains Employee, Department, January, February, March, and April. You want to retain Employee and Department while transforming every monthly column into rows.

1. Select Employee and Department.
2. Right-click one of the selected column headers.
3. Select Unpivot Other Columns.
4. Power Query keeps Employee and Department unchanged.
5. The remaining monthly columns become Attribute and Value rows.

This method can make a query more adaptable when a new month is added to the source. Because the transformation operates on the other columns, a newly added month can be included during refresh, provided the preceding query steps and source schema allow it.

10. Renaming Attribute and Value Columns

After unpivoting, Power Query typically creates columns called Attribute and Value. These names are generic, so renaming them makes the dataset easier to understand.

For example, a table with Employee, Attribute, and Value can be renamed to Employee, Month, and Sales.

Use names that describe the business meaning of the data. This improves readability in Power Query, the Power BI model, and report visuals.

11. Practical Example: Monthly Sales Analysis

Imagine a company maintains monthly sales figures in an Excel spreadsheet. Each employee has a row, and each month has a separate column. The company wants to create a line chart showing sales trends over time.

11.1 Original Dataset

11.2 Transformation Steps

1. Import the spreadsheet into Power BI.
2. Open Power Query Editor.
3. Select Employee and Region.
4. Choose Unpivot Other Columns.
5. Rename Attribute to Month and Value to Sales.
6. Set Employee and Region to appropriate text types.
7. Set Sales to a numeric or currency-compatible type.
8. Convert Month into a date or month-number representation if chronological sorting is required.
9. Validate the transformed records.
10. Load the data into the Power BI model.

11.3 Expected Output

11.4 Creating a Report

Once the data has been unpivoted and loaded, you can create a line chart with Month on the X-axis and Sales on the Y-axis. Employee and Region can be used as filters or slicers.

For accurate chronological sorting, avoid relying only on month names. If data covers multiple years, use a real date or a Year-Month field so that January from different years is not incorrectly combined.

12. Practical Example: Pivoting Survey Results

Consider a survey dataset in which respondents provide ratings for different service categories.

12.1 Source Data

12.2 Pivoted Output

To achieve this result, select Category, choose Pivot Column, and use Rating as the values column. If there is exactly one rating per respondent and category, an aggregation may not be necessary. If multiple responses exist for the same combination, determine whether to use an average, maximum, latest response, or another rule based on the survey requirements.

13. Pivoting and Unpivoting with Power Query M

Power Query records transformations as M language expressions. When you use the graphical interface, Power Query usually generates the required code automatically. You can inspect or edit the expressions through the Advanced Editor.

13.1 Example of Unpivoting Columns

The following example transforms the January, February, and March columns into Attribute-Value rows.

let
    Source = #table(
        {"Employee", "January", "February", "March"},
        {
            {"Amit", 12000, 15000, 14000},
            {"Neha", 10000, 13000, 16000}
        }
    ),
    UnpivotedData = Table.Unpivot(
        Source,
        {"January", "February", "March"},
        "Month",
        "Sales"
    )
in
    UnpivotedData

Explanation:

• Source creates a sample table.
• Table.Unpivot converts the specified monthly columns into rows.
• Month becomes the name of the column containing the original column names.
• Sales becomes the name of the column containing the values.
• The final expression returns the transformed table.

13.2 Example of Unpivoting Other Columns

When the identifier columns are known but the value columns may change, use Table.UnpivotOtherColumns.

let
    Source = #table(
        {"Employee", "Region", "January", "February", "March"},
        {
            {"Amit", "West", 12000, 15000, 14000},
            {"Neha", "North", 10000, 13000, 16000}
        }
    ),
    UnpivotedData = Table.UnpivotOtherColumns(
        Source,
        {"Employee", "Region"},
        "Month",
        "Sales"
    )
in
    UnpivotedData

In this example, Employee and Region remain unchanged. All other columns are converted into rows.

13.3 Example of Pivoting a Column

The following example converts month values into separate columns. It uses Sum as the aggregation function to handle multiple records for the same department and month.

let
    Source = #table(
        {"Department", "Month", "Sales"},
        {
            {"Sales", "January", 20000},
            {"Sales", "January", 30000},
            {"Sales", "February", 40000},
            {"Marketing", "January", 15000},
            {"Marketing", "February", 18000}
        }
    ),
    PivotedData = Table.Pivot(
        Source,
        List.Distinct(Source[Month]),
        "Month",
        "Sales",
        List.Sum
    )
in
    PivotedData

Explanation:

• List.Distinct(Source[Month]) obtains the distinct month values to use as column names.
• "Month" identifies the column whose values become new columns.
• "Sales" identifies the values to place in those columns.
• List.Sum combines multiple values that map to the same output cell.

Note: M expressions depend on the actual source schema and data types. When adapting these examples, confirm that the column names match your query and that the selected aggregation makes business sense.

14. Handling Null Values During Pivoting and Unpivoting

Null values require careful handling because a blank cell can mean different things. It may indicate that no transaction occurred, that data was not collected, or that the source contains an incomplete record.

14.1 Null Values During Unpivoting

When unpivoting, Power Query commonly omits rows for null values in the columns being unpivoted. This can reduce unnecessary blank records, but it can also remove information if the absence of a value is meaningful.

• Inspect the source before unpivoting.
• Determine whether blank values mean zero, not applicable, or unknown.
• Do not automatically replace null with zero unless that interpretation is correct.
• Validate the number of records before and after the transformation.

14.2 Null Values During Pivoting

When pivoting, combinations without a matching source value may appear as null in the resulting table. This is normal when no source record exists for that grouping and pivot value.

For example, if Marketing has no February record, the February cell for Marketing may be null. Replacing it with zero is appropriate only if the business rule says that a missing record represents zero sales.

15. Managing Data Types After Transformation

Pivoting and unpivoting change the structure of a table, so the resulting columns should be reviewed for correct data types.

For example, a month name such as January is text, not a date. If you want to sort months chronologically, create a month number or a real date column and use it to control sorting.

16. Sorting Months Correctly in Power BI

After unpivoting monthly columns, the Month field may contain text values such as January, February, and March. Alphabetical sorting does not follow calendar order.

16.1 The Problem

Alphabetical sorting can display the months in an order such as April, August, December, February, and January rather than calendar order.

16.2 Recommended Solution

1. Create a Month Number column with values from 1 to 12.
2. Ensure each month name is consistently associated with the correct number.
3. Load the data into the Power BI model.
4. Select the Month column in the model or Data view.
5. Choose Column tools > Sort by column.
6. Select Month Number.

If the data includes several years, use a complete date or Year-Month column for time-based analysis. A month number alone cannot distinguish January of one year from January of another year.

17. Common Errors and Troubleshooting

17.1 Error: Cannot Pivot Because Multiple Values Exist

Cause: More than one source value maps to the same combination of grouping columns and pivot column value.

Solution: Identify the duplicate combinations. If the records are valid transactions, choose an appropriate aggregation such as Sum or Average. If the records are unintended duplicates, investigate and clean the source data before pivoting.

17.2 Error: Unexpected Number of Rows After Unpivoting

Cause: Some source columns contain null values, or the selected columns do not match the intended transformation.

Solution: Compare the source and output, check the selected columns, and determine whether blank values should be retained or excluded. Remember that null-valued cells may not produce output rows during unpivoting.

17.3 Error: New Columns Are Missing After Refresh

Cause: A query may explicitly select a fixed set of columns, or an earlier step may remove newly added fields.

Solution: Review the Applied Steps and use Unpivot Other Columns when appropriate. Check steps such as Choose Columns, Removed Other Columns, or type transformations that depend on a fixed schema.

17.4 Error: Values Have the Wrong Data Type

Cause: The source contains mixed data types, text-formatted numbers, currency symbols, or inconsistent values.

Solution: Clean the source values and assign the appropriate type after transformation. If numbers are stored as text because of regional formatting, verify the decimal and thousands separators.

17.5 Error: Duplicate Output Rows

Cause: The remaining grouping columns are not unique at the desired output level, or the source has multiple records per entity and category.

Solution: Determine the intended grain of the dataset. Retain the columns needed to distinguish records or aggregate the source data before pivoting.

17.6 Error: Month Values Are Sorted Incorrectly

Cause: Month names are being sorted alphabetically.

Solution: Add a month number or proper date field and use it for sorting.

18. Best Practices for Pivoting and Unpivoting

• Understand the business meaning: Know what each row represents before changing the structure.
• Keep identifiers intact: Preserve fields such as Employee ID, Product ID, Region, and Date when they are needed to identify observations.
• Choose aggregation carefully: Sum, Average, Count, Minimum, and Maximum are not interchangeable.
• Prefer consistent schemas: Use stable column names and predictable data types.
• Use Unpivot Other Columns thoughtfully: It is useful for expanding period columns, but confirm that every non-identifier column should be transformed.
• Rename generic columns: Replace Attribute and Value with business-friendly names.
• Check missing data: Decide whether nulls should remain null, be replaced, or be investigated.
• Validate row counts: Confirm that the resulting dataset contains the expected observations.
• Check duplicates: Understand why repeated key combinations exist before aggregating.
• Use correct sorting: Add date or numeric sort columns for periods and categories that require a meaningful order.
• Review Applied Steps: Ensure earlier transformations do not unintentionally remove required columns.
• Test refresh behavior: Confirm that the query continues to work when new rows, months, or categories are added.

19. Difference Between Pivoting and Unpivoting

20. Using Pivoting and Unpivoting in Business Scenarios

20.1 Sales Reporting

Unpivot monthly sales columns to create a consistent Month and Sales structure. This supports trend charts, comparisons, and filtering by region or salesperson.

20.2 Financial Reporting

Financial spreadsheets may contain separate columns for revenue, expenses, and profit by period. Unpivoting the period columns can create a reusable Period and Amount structure, provided the measure names and values are modeled appropriately.

20.3 Inventory Management

Inventory reports may show product quantities for multiple warehouses in separate columns. Unpivoting the warehouse columns can create a Warehouse and Quantity structure for stock analysis.

20.4 Survey Analysis

Pivot survey categories into columns when a report requires one row per respondent and one column per question category. Unpivot them when analysis requires a consistent Question and Rating structure.

20.5 Employee Performance

Performance reports may contain separate columns for quarterly targets or scores. Unpivoting these columns allows a report to compare performance across employees and periods using consistent fields.

21. Practice Exercise

Use the following dataset to practice unpivoting.

Tasks

1. Import the dataset into Power Query Editor.
2. Keep the Product column unchanged.
3. Unpivot the Q1, Q2, Q3, and Q4 columns.
4. Rename Attribute to Quarter and Value to Quantity.
5. Set Quantity to a suitable numeric data type.
6. Check the number of rows in the resulting table.
7. Create a column chart showing Quantity by Quarter and Product.
8. Consider how the query would behave if Q5 were added to the source.

Expected Result

The output should contain 12 rows: three products multiplied by four quarters.

22. Mini Project: Prepare a Monthly Performance Dashboard

Project objective: Transform a wide-format monthly performance spreadsheet into a long-format dataset that can be used for a Power BI dashboard.

Project Requirements

• Import an Excel file containing employee, department, and monthly performance columns.
• Clean column names and remove unnecessary records.
• Use Unpivot Other Columns to transform the monthly values into rows.
• Rename the generated Attribute and Value columns.
• Apply suitable data types.
• Check for null values, duplicate records, and incorrect values.
• Create an appropriate date or month-number field for chronological sorting.
• Load the cleaned data into Power BI.
• Build visuals comparing performance by employee, department, and month.

Expected Deliverables

1. A cleaned and transformed Power Query dataset.
2. A documented set of Applied Steps.
3. A Power BI report showing performance trends.
4. A short explanation of the transformation choices and data-quality checks.

23. Interview Questions and Answers

Q1. What is pivoting in Power Query?

Answer: Pivoting converts distinct values from a selected column into separate columns and places values from another column into those columns.

Q2. What is unpivoting in Power Query?

Answer: Unpivoting converts selected columns into rows and creates Attribute and Value columns to represent the original column names and their values.

Q3. What is the difference between Pivot Column and Unpivot Columns?

Answer: Pivot Column changes category values into columns, while Unpivot Columns changes selected columns into category-value rows.

Q4. When should you use Unpivot Other Columns?

Answer: Use it when you want to preserve known identifier columns and transform all remaining columns into rows. It is especially useful when additional period columns may be added to the source.

Q5. Why might pivoting require an aggregation function?

Answer: An aggregation may be required when multiple source values map to the same output cell. The function combines those values according to a defined rule.

Q6. Which aggregation function should be used for sales data?

Answer: Sum is often appropriate for transaction-level sales, but the correct function depends on the dataset's meaning and grain. Average, Maximum, or another function may be suitable for other measures.

Q7. What are Attribute and Value columns?

Answer: Attribute stores the names of the original columns that were unpivoted, and Value stores the corresponding values from those columns.

Q8. Does unpivoting always preserve every source cell?

Answer: No. Null values in unpivoted columns may not generate output rows. Always check whether missing values have a business meaning that must be preserved.

Q9. Why is unpivoting useful for Power BI reports?

Answer: It creates a consistent structure that supports flexible filtering, grouping, charts, and time-based analysis. It can also simplify refreshes when new period columns are introduced.

Q10. How can you sort month names chronologically?

Answer: Create a month number or appropriate date column, then use Sort by column to sort the month labels in calendar order.

Q11. What is the purpose of Table.UnpivotOtherColumns?

Answer: It converts all columns other than the specified identifier columns into Attribute-Value rows.

Q12. Can pivoting and unpivoting be reversed?

Answer: They can often reverse each other's structural changes when the data is complete and the original values are unique at the required grain. However, aggregation, removed nulls, or lost distinctions can prevent an exact reconstruction.

Q13. How do you troubleshoot missing columns after refresh?

Answer: Review the Applied Steps and inspect fixed-column selection steps. Consider Unpivot Other Columns if the requirement is to transform all non-identifier columns dynamically.

Q14. What should you check before pivoting?

Answer: Check the grouping columns, the pivot column, the values column, data types, duplicate combinations, and the appropriate aggregation rule.

Q15. Where can you find the M code for a transformation?

Answer: You can inspect the generated expression in the formula bar or open the Advanced Editor in Power Query Editor.

24. Quick Revision Checklist

• Pivoting converts row values into columns.
• Unpivoting converts selected columns into rows.
• Pivoting may require an aggregation function.
• Unpivoting creates Attribute and Value columns.
• Unpivot Columns transforms the selected columns.
• Unpivot Other Columns preserves the selected columns and transforms the rest.
• Rename generic columns to reflect their business meaning.
• Check data types after every structural transformation.
• Investigate duplicate combinations before aggregating.
• Review how null values affect the resulting table.
• Use a date or numeric sort column for chronological order.
• Inspect Applied Steps when refresh behavior is unexpected.
• Validate row counts and sample records before loading data.

25. Conclusion

Pivoting and unpivoting are essential Power Query transformations for reshaping data in Microsoft Power BI. Pivoting turns category values into columns, while unpivoting converts selected columns into attribute-value rows.

These transformations help you prepare spreadsheet reports, monthly sales data, inventory records, survey results, and employee performance datasets for analysis. Successful transformation depends on understanding the source data, preserving the correct identifiers, selecting suitable aggregation rules, and checking null values and data types.

By practicing these techniques and inspecting the generated M code, you can build more flexible and reliable data preparation workflows. Always validate the output and refresh behavior before using transformed data in a business dashboard.

Additional Learning Resources

• Explore Microsoft Power BI Training at JustAcademy
• Register for a Power BI Course Demo
• Visit JustAcademy